﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Linq.Expressions;
using System.Threading.Tasks;
using Dapper;
using Microsoft.Data.Sqlite;
using AyuBookmark.Common.Interfaces;
using AyuBookmark.Common.Models;
using AyuBookmark.Common.Extensions;

namespace AyuBookmark.Common.Repositories
{
    /// <summary>
    /// 基于Dapper和SQLite的通用仓储基类
    /// </summary>
    /// <typeparam name="TEntity">实体类型</typeparam>
    /// <typeparam name="TKey">主键类型</typeparam>
    public abstract class BaseRepository<TEntity, TKey> : IRepository<TEntity, TKey>
        where TEntity : class
    {
        private readonly string _connectionString;
        private readonly string _tableName;

        /// <summary>
        /// 初始化基础仓储
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="tableName">表名（可选）</param>
        protected BaseRepository(string connectionString, string tableName = null)
        {
            _connectionString = connectionString ?? throw new ArgumentNullException(nameof(connectionString));
            _tableName = tableName ?? typeof(TEntity).Name + "s";
        }

        /// <summary>
        /// 获取数据库连接（自动管理生命周期）
        /// </summary>
        protected virtual async Task<IDbConnection> GetConnectionAsync()
        {
            var connection = new SqliteConnection(_connectionString);
            await connection.OpenAsync();
            return connection;
        }

        /// <summary>
        /// 映射数据库列名（可重写实现自定义映射）
        /// </summary>
        protected virtual string MapColumn(string propertyName) => propertyName;

        #region 基础CRUD操作
        /// <inheritdoc />
        //public virtual async Task<TEntity> GetByIdAsync(TKey id)
        //{
        //    using var connection = await GetConnectionAsync();
        //    return await connection.QueryFirstOrDefaultAsync<TEntity>(
        //        $"SELECT * FROM {_tableName} WHERE Id = @id",
        //        new { id });
        //}
        public virtual async Task<TEntity> GetByIdAsync(TKey id)
        {
            using var connection = await GetConnectionAsync();
            return await connection.QueryFirstOrDefaultAsync<TEntity>(
                $"SELECT * FROM {_tableName} WHERE Id = @id",
                new { id }.ToDynamicParameters()); // 使用新的扩展方法
        }
        /// <inheritdoc />
        public virtual async Task<IEnumerable<TEntity>> GetAllAsync()
        {
            using var connection = await GetConnectionAsync();
            return await connection.QueryAsync<TEntity>(
                $"SELECT * FROM {_tableName}");
        }

        /// <inheritdoc />
        public virtual async Task<TEntity> AddAsync(TEntity entity)
        {
            var properties = typeof(TEntity).GetProperties()
                .Where(p => p.Name != "Id" && p.CanWrite)
                .Select(p => new { Name = MapColumn(p.Name), Value = p.GetValue(entity) })
                .ToDictionary(x => x.Name, x => x.Value);

            var columns = string.Join(", ", properties.Keys);
            var values = string.Join(", ", properties.Keys.Select(k => "@" + k));

            using var connection = await GetConnectionAsync();
            var sql = $"INSERT INTO {_tableName} ({columns}) VALUES ({values}); SELECT last_insert_rowid();";

            // 获取插入后的ID并反射设置到实体
            var id = await connection.ExecuteScalarAsync<TKey>(sql, properties);
            if (typeof(TEntity).GetProperty("Id") != null)
            {
                typeof(TEntity).GetProperty("Id")?.SetValue(entity, id);
            }

            return entity;
        }

        /// <inheritdoc />
        //public virtual async Task<bool> UpdateAsync(TEntity entity)
        //{
        //    var properties = typeof(TEntity).GetProperties()
        //        .Where(p => p.Name != "Id" && p.CanWrite)
        //        .Select(p => new { Name = MapColumn(p.Name), Value = p.GetValue(entity) })
        //        .ToDictionary(x => x.Name, x => x.Value);

        //    var id = typeof(TEntity).GetProperty("Id")?.GetValue(entity);
        //    if (id == null) throw new InvalidOperationException("Entity must have an Id property");

        //    var setClause = string.Join(", ", properties.Keys.Select(k => $"{k} = @{k}"));

        //    using var connection = await GetConnectionAsync();

        //    // 使用新的扩展方法
        //    var dynamicParams = new DynamicParameters();
        //    dynamicParams.Add("id", id);
        //    dynamicParams.MergeParameters(properties);

        //    var affectedRows = await connection.ExecuteAsync(
        //        $"UPDATE {_tableName} SET {setClause} WHERE Id = @id",
        //        dynamicParams);

        //    return affectedRows > 0;
        //}

        public virtual async Task<bool> UpdateAsync(TEntity entity)
        {
            var properties = typeof(TEntity).GetProperties()
                .Where(p => p.Name != "Id" && p.CanWrite)
                .Select(p => new { Name = MapColumn(p.Name), Value = p.GetValue(entity) })
                .ToDictionary(x => x.Name, x => x.Value);

            var id = typeof(TEntity).GetProperty("Id")?.GetValue(entity);
            if (id == null) throw new InvalidOperationException("Entity must have an Id property");

            var setClause = string.Join(", ", properties.Keys.Select(k => $"{k} = @{k}"));

            using var connection = await GetConnectionAsync();
            var dynamicParams = new DynamicParameters();
            dynamicParams.Add("id", id);
            dynamicParams.MergeParameters(properties); // 使用扩展方法

            var affectedRows = await connection.ExecuteAsync(
                $"UPDATE {_tableName} SET {setClause} WHERE Id = @id",
                dynamicParams);

            return affectedRows > 0;
        }

        /// <inheritdoc />
        public virtual async Task<bool> DeleteAsync(TEntity entity)
        {
            var id = typeof(TEntity).GetProperty("Id")?.GetValue(entity);
            if (id == null) throw new InvalidOperationException("Entity must have an Id property");

            return await DeleteByIdAsync((TKey)id);
        }

        /// <inheritdoc />
        public virtual async Task<bool> DeleteByIdAsync(TKey id)
        {
            using var connection = await GetConnectionAsync();
            var affectedRows = await connection.ExecuteAsync(
                $"DELETE FROM {_tableName} WHERE Id = @id",
                new { id });

            return affectedRows > 0;
        }
        #endregion

        #region 高级查询
        /// <inheritdoc />
        public virtual async Task<IEnumerable<TEntity>> FindAsync(Expression<Func<TEntity, bool>> predicate)
        {
            var (where, parameters) = BuildWhereClause(predicate);
            using var connection = await GetConnectionAsync();
            return await connection.QueryAsync<TEntity>(
                $"SELECT * FROM {_tableName} WHERE {where}",
                parameters);
        }

        /// <inheritdoc />
        public virtual async Task<PaginatedResult<TEntity>> GetPagedAsync(
            Expression<Func<TEntity, bool>> predicate = null,
            int pageIndex = 1,
            int pageSize = 10,
            Expression<Func<TEntity, object>> orderBy = null,
            bool isDescending = true)
        {
            if (pageIndex < 1) throw new ArgumentOutOfRangeException(nameof(pageIndex));
            if (pageSize < 1) throw new ArgumentOutOfRangeException(nameof(pageSize));

            var wherePart = "";
            object parameters = null;

            if (predicate != null)
            {
                var (where, param) = BuildWhereClause(predicate);
                wherePart = "WHERE " + where;
                parameters = param;
            }

            var orderByColumn = "Id";
            if (orderBy != null)
            {
                if (orderBy.Body is MemberExpression memberExpression)
                {
                    orderByColumn = MapColumn(memberExpression.Member.Name);
                }
            }

            var direction = isDescending ? "DESC" : "ASC";
            var offset = (pageIndex - 1) * pageSize;

            using var connection = await GetConnectionAsync();
            var sql = $@"
                SELECT * FROM {_tableName}
                {wherePart}
                ORDER BY {orderByColumn} {direction}
                LIMIT {pageSize} OFFSET {offset};

                SELECT COUNT(*) FROM {_tableName} {wherePart};
            ";

            using var multi = await connection.QueryMultipleAsync(sql, parameters);
            var items = await multi.ReadAsync<TEntity>();
            var totalCount = await multi.ReadFirstAsync<int>();

            return new PaginatedResult<TEntity>
            {
                PageIndex = pageIndex,
                PageSize = pageSize,
                TotalCount = totalCount,
                Items = items
            };
        }

        /// <inheritdoc />
        public virtual async Task<int> CountAsync(Expression<Func<TEntity, bool>> predicate = null)
        {
            var wherePart = "";
            object parameters = null;

            if (predicate != null)
            {
                var (where, param) = BuildWhereClause(predicate);
                wherePart = "WHERE " + where;
                parameters = param;
            }

            using var connection = await GetConnectionAsync();
            return await connection.ExecuteScalarAsync<int>(
                $"SELECT COUNT(*) FROM {_tableName} {wherePart}",
                parameters);
        }

        /// <inheritdoc />
        public virtual async Task<bool> ExistsAsync(Expression<Func<TEntity, bool>> predicate)
        {
            var (where, parameters) = BuildWhereClause(predicate);
            using var connection = await GetConnectionAsync();
            return await connection.ExecuteScalarAsync<bool>(
                $"SELECT EXISTS(SELECT 1 FROM {_tableName} WHERE {where})",
                parameters);
        }
        #endregion

        #region 批量操作
        /// <inheritdoc />
        public virtual async Task<int> AddRangeAsync(IEnumerable<TEntity> entities)
        {
            using var connection = await GetConnectionAsync();
            using var transaction = connection.BeginTransaction();

            try
            {
                var count = 0;
                foreach (var entity in entities)
                {
                    await AddAsync(entity);
                    count++;
                }

                transaction.Commit();
                return count;
            }
            catch
            {
                transaction.Rollback();
                throw;
            }
        }

        /// <inheritdoc />
        public virtual async Task<int> DeleteWhereAsync(Expression<Func<TEntity, bool>> predicate)
        {
            var (where, parameters) = BuildWhereClause(predicate);
            using var connection = await GetConnectionAsync();
            return await connection.ExecuteAsync(
                $"DELETE FROM {_tableName} WHERE {where}",
                parameters);
        }
        #endregion

        #region SQL直接执行
        /// <inheritdoc />
        public virtual async Task<IEnumerable<TEntity>> QueryAsync(string sql, object parameters = null)
        {
            using var connection = await GetConnectionAsync();
            return await connection.QueryAsync<TEntity>(sql, parameters);
        }

        /// <inheritdoc />
        public virtual async Task<int> ExecuteAsync(string sql, object parameters = null)
        {
            using var connection = await GetConnectionAsync();
            return await connection.ExecuteAsync(sql, parameters);
        }
        #endregion

        #region 辅助方法
        /// <summary>
        /// 构建WHERE条件子句（支持简单表达式）
        /// </summary>
        protected virtual (string whereClause, object parameters) BuildWhereClause(Expression<Func<TEntity, bool>> predicate)
        {
            // 简化版：仅支持 Member == Constant 的表达式
            if (predicate.Body is BinaryExpression binaryExpression)
            {
                if (binaryExpression.NodeType == ExpressionType.Equal)
                {
                    if (binaryExpression.Left is MemberExpression left &&
                        binaryExpression.Right is ConstantExpression right)
                    {
                        var columnName = MapColumn(left.Member.Name);
                        return ($"{columnName} = @value", new { value = right.Value });
                    }
                }
            }

            // 复杂表达式需要引入完整的表达式解析器（如使用System.Linq.Dynamic.Core）
            throw new NotSupportedException("Complex expressions are not supported in this implementation. " +
                "Override BuildWhereClause in derived class for advanced query support.");
        }

        ///// <summary>
        ///// 动态参数合并扩展
        ///// </summary>
        //protected static DynamicParameters Merge(this DynamicParameters source, IDictionary<string, object> additional)
        //{
        //    foreach (var item in additional)
        //    {
        //        source.Add(item.Key, item.Value);
        //    }
        //    return source;
        //}
        #endregion
    }


    /// <summary>
    /// 书签仓储实现
    /// </summary>
    //public class BookmarkRepository : BaseRepository<Bookmark, int>, IBookmarkRepository
    //{
    //    public BookmarkRepository(string connectionString) : base(connectionString, "Bookmarks") { }

    //    public async Task<Bookmark> GetByUrlAsync(string url)
    //    {
    //        using var connection = await GetConnectionAsync();
    //        return await connection.QueryFirstOrDefaultAsync<Bookmark>(
    //            "SELECT * FROM Bookmarks WHERE Url = @url OR UrlHash = @urlHash",
    //            new { url, urlHash = HashHelper.Sha256(url) });
    //    }

    //    public async Task<IEnumerable<Bookmark>> GetByCategoryAsync(int categoryId)
    //    {
    //        using var connection = await GetConnectionAsync();
    //        return await connection.QueryAsync<Bookmark>(
    //            "SELECT * FROM Bookmarks WHERE CategoryId = @categoryId",
    //            new { categoryId });
    //    }

    //    public async Task<int> UpdateCategoryAsync(IEnumerable<int> bookmarkIds, int categoryId)
    //    {
    //        using var connection = await GetConnectionAsync();
    //        return await connection.ExecuteAsync(
    //            "UPDATE Bookmarks SET CategoryId = @categoryId WHERE Id IN @bookmarkIds",
    //            new { bookmarkIds, categoryId });
    //    }

    //    protected override (string whereClause, object parameters) BuildWhereClause(Expression<Func<Bookmark, bool>> predicate)
    //    {
    //        // 实现自定义的复杂表达式解析
    //        // 这里可以使用System.Linq.Dynamic.Core等库
    //        return base.BuildWhereClause(predicate);
    //    }
    //}

    /// <summary>
    /// 哈希辅助类
    /// </summary>
    internal static class HashHelper
    {
        public static string Sha256(string input)
        {
            using var sha = System.Security.Cryptography.SHA256.Create();
            var bytes = System.Text.Encoding.UTF8.GetBytes(input);
            var hash = sha.ComputeHash(bytes);
            return BitConverter.ToString(hash).Replace("-", "").ToLower();
        }
    }

}